CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_INDEX_WEEKEND" is
    /*--------------------------------------- 计算表STOCK_INDEX_WEEKEND的所有记录 ---------------------------------------*/
    procedure CAL_STK_IDX_WKD is
        -- 表示index_code字段
        v_index_code varchar2(10);
        -- 表示下一个星期一
        v_first_next_monday date;
        -- 定义一个含有5个stock_index记录的数组
        type stock_index_weekend_type_array is varray(10000) of stock_index%rowtype;
        t_stock_index_weekend_record stock_index_weekend_type_array := stock_index_weekend_type_array();
        -- 表示一个星期中的开盘价，收盘价，最高价和最低价
        v_stock_index_weekend_open  number;
        v_stock_index_weekend_close number;
        v_stock_index_weekend_high  number;
        v_stock_index_weekend_low   number;
        -- 表示这个星期的交易日数
        v_day_num_in_week number;
        -- 表示这个星期这只指数的总成交量
        v_weekend_amount number;
        -- 查询所有的指数的index_code，去除重复。
        cursor cur_all_index_code is
            select distinct t.index_code from stock_index t;
        -- 根据变量v_index_code查找相应的指数的所有记录，并按升序排列
        cursor cur_single_stock_index is
            select *
              from stock_index t
             where t.index_code = v_index_code
             order by t.index_date asc;
        -- 表示STOCK_INDEX类型的记录
        rt_single_stock_index stock_index%rowtype;
    begin
        -- 需要先给v_weekend_amount赋初值，否则第一个指数的第一个交易周将没有成交量
        v_weekend_amount := 0;

        -- 计算普通K线的开盘价，收盘价，最高价和最低价
        open cur_all_index_code;
        loop
            fetch cur_all_index_code
                into v_index_code;
            exit when cur_all_index_code%notfound;

            -- v_first_next_monday。next_day函数的第二个参数为1时表示星期日，为2时才表示星期一。
            select next_day(t.index_date, 2)
              into v_first_next_monday
              from stock_index t
             where t.index_code = v_index_code
               and rownum <= 1
             order by t.index_date asc;

            -- 注意：如果不添加这一行，每个指数都会出现开始日期大于结束日期的记录（第一条记录除外）
            t_stock_index_weekend_record := stock_index_weekend_type_array();

            open cur_single_stock_index;
            loop
                fetch cur_single_stock_index
                    into rt_single_stock_index;
                exit when cur_single_stock_index%notfound;

                if rt_single_stock_index.index_date < v_first_next_monday then
                    -- 如果是属于同一个星期的记录就保存到数组中
                    t_stock_index_weekend_record.extend;
                    t_stock_index_weekend_record(t_stock_index_weekend_record.count) := rt_single_stock_index;
                else
                    -- 计算一个星期中的开盘价，收盘价，最高价和最低价
                    v_stock_index_weekend_open  := t_stock_index_weekend_record(1)
                                                   .index_open;
                    v_stock_index_weekend_close := t_stock_index_weekend_record(t_stock_index_weekend_record.count)
                                                   .index_close;
                    if v_stock_index_weekend_open >
                       v_stock_index_weekend_close then
                        v_stock_index_weekend_high := v_stock_index_weekend_open;
                        v_stock_index_weekend_low  := v_stock_index_weekend_close;
                    end if;
                    if v_stock_index_weekend_open <
                       v_stock_index_weekend_close then
                        v_stock_index_weekend_high := v_stock_index_weekend_close;
                        v_stock_index_weekend_low  := v_stock_index_weekend_open;
                    end if;
                    for x in 1 .. t_stock_index_weekend_record.count loop
                        if t_stock_index_weekend_record(x)
                         .index_high > v_stock_index_weekend_high then
                            v_stock_index_weekend_high := t_stock_index_weekend_record(x)
                                                          .index_high;
                        end if;
                    end loop;
                    for x in 1 .. t_stock_index_weekend_record.count loop
                        if t_stock_index_weekend_record(x)
                         .index_low < v_stock_index_weekend_low then
                            v_stock_index_weekend_low := t_stock_index_weekend_record(x)
                                                         .index_low;
                        end if;
                    end loop;
                    -- 这个星期这只股票的总成交量
                    for x in 1 .. t_stock_index_weekend_record.count loop
                        v_weekend_amount := v_weekend_amount + t_stock_index_weekend_record(x)
                                           .amount;
                    end loop;

                    -- 插入这个星期的数据
                    v_day_num_in_week := t_stock_index_weekend_record.count;
                    insert into stock_index_weekend
                        (INDEX_CODE,
                         INDEX_BEGIN_DATE,
                         INDEX_END_DATE,
                         INDEX_OPEN,
                         INDEX_CLOSE,
                         INDEX_HIGH,
                         INDEX_LOW,
                         AMOUNT,
                         HA_INDEX_OPEN,
                         HA_INDEX_CLOSE,
                         HA_INDEX_HIGH,
                         HA_INDEX_LOW)
                    values
                        (v_index_code,
                         t_stock_index_weekend_record(1).index_date,
                         t_stock_index_weekend_record(v_day_num_in_week)
                         .index_date,
                         v_stock_index_weekend_open,
                         v_stock_index_weekend_close,
                         v_stock_index_weekend_high,
                         v_stock_index_weekend_low,
                         v_weekend_amount,
                         null,
                         null,
                         null,
                         null);
                    commit;
                    -- 为下一轮迭代做准备
                    select next_day(rt_single_stock_index.index_date, 2)
                      into v_first_next_monday
                      from dual;
                    t_stock_index_weekend_record := stock_index_weekend_type_array();
                    t_stock_index_weekend_record.extend;
                    t_stock_index_weekend_record(t_stock_index_weekend_record.count) := rt_single_stock_index;
                    v_weekend_amount := 0;
                end if;
            end loop;
            close cur_single_stock_index;

        end loop;
        close cur_all_index_code;
    end CAL_STK_IDX_WKD;

    /*---------------------------------- 计算表STOCK_INDEX_WEEKEND的所有Hei Kin Ashi字段 -----------------------------*/
    procedure CAL_STK_IDX_WKD_HA is
        -- 获取所有的INDEX_CODE
        cursor cur_all_index_code is
            select distinct t.index_code index_code from stock_index t;
        -- 定义表index_code结构的游标变量
        v_index_code varchar2(10);

        -- 查询某个具体的指数的所有交易记录，并按升序排列
        cursor cur_all_stock_index_weekend is
            select *
              from stock_index_weekend t
             where t.index_code = v_index_code
             order by t.index_end_date asc;
        -- 定义表stock_index结构的游标变量，用于接收游标cur_all_stock_index的记录
        all_stock_index_weekend stock_index_weekend%rowtype;

        -- 查询某个指数（周线级别）除了最早的一条记录外的其他记录，并按升序排列
        cursor cur_later_stock_index_weekend is
            select *
              from stock_index_weekend t
             where t.index_code = v_index_code
               and t.index_end_date <>
                   (select min(t1.index_end_date)
                      from stock_index_weekend t1
                     where t1.index_code = v_index_code)
             order by t.index_end_date asc;
        -- 定义表stock_index_weekend结构的游标变量，用于接收游标cur_later_stock_index_weekend的记录
        later_stock_index_weekend stock_index_weekend%rowtype;

        -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
        v_ha_index_open  number;
        v_ha_index_close number;
        v_ha_index_high  number;
        v_ha_index_low   number;
    begin
        open cur_all_index_code;
        loop
            -- 获取每个指数的index_code字段
            fetch cur_all_index_code
                into v_index_code;
            exit when cur_all_index_code%notfound;

            open cur_all_stock_index_weekend;
            open cur_later_stock_index_weekend;
            loop
                fetch cur_all_stock_index_weekend
                    into all_stock_index_weekend;
                exit when cur_all_stock_index_weekend%notfound;

                fetch cur_later_stock_index_weekend
                    into later_stock_index_weekend;
                exit when cur_later_stock_index_weekend%notfound;

                -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
                v_ha_index_open  := (all_stock_index_weekend.index_open +
                                    all_stock_index_weekend.index_close) / 2;
                v_ha_index_close := (later_stock_index_weekend.index_open +
                                    later_stock_index_weekend.index_close +
                                    later_stock_index_weekend.index_high +
                                    later_stock_index_weekend.index_low) / 4;
                v_ha_index_high  := fnc_ha_max_value(later_stock_index_weekend.index_high,
                                                     later_stock_index_weekend.index_open,
                                                     later_stock_index_weekend.index_close);
                v_ha_index_low   := fnc_ha_min_value(later_stock_index_weekend.index_low,
                                                     later_stock_index_weekend.index_open,
                                                     later_stock_index_weekend.index_close);
                -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
                update stock_index_weekend t
                   set t.ha_index_open  = v_ha_index_open,
                       t.ha_index_close = v_ha_index_close,
                       t.ha_index_high  = v_ha_index_high,
                       t.ha_index_low   = v_ha_index_low
                 where t.index_code = later_stock_index_weekend.index_code
                   and t.index_end_date =
                       later_stock_index_weekend.index_end_date;
                commit;
            end loop;
            close cur_later_stock_index_weekend;
            close cur_all_stock_index_weekend;
        end loop;
        close cur_all_index_code;
    end CAL_STK_IDX_WKD_HA;

    /*-------------------------------- 按照日期，计算表STOCK_INDEX_WEEKEND的所有记录 ---------------------------------*/
    procedure CAL_STK_IDX_WKD_BD(p_begin_date in varchar2,
                                 p_end_date   in varchar2) is
        -- 表示某个指数的INDEX_CODE字段
        v_index_code varchar2(10);
        -- 分别代表某只股票在某个星期的最高价，最低价，开盘价，收盘价，成交量和序列号
        v_high_price  number;
        v_low_price   number;
        v_open_price  number;
        v_close_price number;
        v_sum_amount  number;
        -- 查询从开始时间p_begin_date到p_end_date，表STOCK_INDEX中的记录数
        v_num number;
        -- 获取表STOCK_INDEX中的列INDEX_CODE，去除重复
        cursor cur_all_index_code is
            select distinct t.index_code
              from stock_index t
             order by t.index_code asc;
    begin
        open cur_all_index_code;
        loop
            fetch cur_all_index_code
                into v_index_code;
            exit when cur_all_index_code%notfound;

            -- 查询从开始时间p_begin_date到p_end_date，表STOCK_INDEX中的记录数
            select count(*)
              into v_num
              from stock_index t
             where t.index_code = v_index_code
               and t.index_date between to_date(p_begin_date, 'yyyy-mm-dd') and
                   to_date(p_end_date, 'yyyy-mm-dd');

            if v_num != 0 then
                -- 计算某只股票在某个星期的最高价，最低价，开盘价，收盘价和成交量
                select max(t.index_high), min(t.index_low)
                  into v_high_price, v_low_price
                  from stock_index t
                 where t.index_code = v_index_code
                   and t.index_date between
                       to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                 order by t.index_date asc;

                select t.index_open
                  into v_open_price
                  from stock_index t
                 where t.index_code = v_index_code
                   and t.index_date between
                       to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                   and rownum <= 1
                 order by t.index_date asc;

                select t.index_close
                  into v_close_price
                  from stock_index t
                 where t.index_code = v_index_code
                   and t.index_date between
                       to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                   and rownum <= 1
                 order by t.index_date desc;

                select sum(t.amount)
                  into v_sum_amount
                  from stock_index t
                 where t.index_code = v_index_code
                   and t.index_date between
                       to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd');

                insert into stock_index_weekend
                    (index_code,
                     index_begin_date,
                     index_end_date,
                     index_high,
                     index_low,
                     index_open,
                     index_close,
                     amount)
                values
                    (v_index_code,
                     to_date(p_begin_date, 'yyyy-mm-dd'),
                     to_date(p_end_date, 'yyyy-mm-dd'),
                     v_high_price,
                     v_low_price,
                     v_open_price,
                     v_close_price,
                     v_sum_amount);
                commit;
            end if;
        end loop;
        close cur_all_index_code;
    end CAL_STK_IDX_WKD_BD;

    /*--------------------------- 按照日期，计算表STOCK_INDEX_WEEKEND的Hei Kin Ashi记录 --------------------------------*/
    procedure CAL_STK_IDX_WKD_HA_BD(p_begin_date in varchar2,
                                    p_end_date   in varchar2) is
        -- 获取所有的INDEX_CODE
        cursor cur_all_index_code is
            select distinct t.index_code index_code
              from stock_index_weekend t;
        -- 表示INDEX_CODE类型的变量
        v_index_code varchar2(10);

        -- 查询某个指数在日期p_index_date之前的那一条记录
        cursor cur_all_stock_index_weekend is
            select *
              from (select *
                      from (select *
                              from stock_index_weekend t
                             where t.index_code = v_index_code
                               and t.index_end_date <=
                                   to_date(p_end_date, 'yyyy-mm-dd')
                               and t.index_begin_date <=
                                   to_date(p_begin_date, 'yyyy-mm-dd')
                             order by t.index_end_date desc)
                     where rownum <= 2) t2
             where t2.index_end_date <>
                   (select t1.index_end_date
                      from stock_index_weekend t1
                     where t1.index_code = v_index_code
                       and t1.index_end_date =
                           to_date(p_end_date, 'yyyy-mm-dd'))
               and t2.index_begin_date <>
                   (select t1.index_begin_date
                      from stock_index_weekend t1
                     where t1.index_code = v_index_code
                       and t1.index_begin_date =
                           to_date(p_begin_date, 'yyyy-mm-dd'));
        -- 定义表stock_index_weekend结构的游标变量，用于接收游标cur_later_stock_index_weekend的记录
        all_stock_index_weekend stock_index_weekend%rowtype;

        -- 查询某个具体的指数的某一日交易记录
        cursor cur_later_stock_index_weekend is
            select *
              from stock_index_weekend t
             where t.index_code = v_index_code
               and t.index_begin_date = to_date(p_begin_date, 'yyyy-mm-dd')
               and t.index_end_date = to_date(p_end_date, 'yyyy-mm-dd');
        -- 定义表stock_index_weekend结构的游标变量，用于接收游标cur_all_stock_index_weekend的记录
        later_stock_index_weekend stock_index_weekend%rowtype;

        -- 用于计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价的变量
        v_ha_index_weekend_open  number;
        v_ha_index_weekend_close number;
        v_ha_index_weekend_high  number;
        v_ha_index_weekend_low   number;
    begin
        open cur_all_index_code;
        loop
            -- 获取每个指数的index_code字段
            fetch cur_all_index_code
                into v_index_code;
            exit when cur_all_index_code%notfound;

            open cur_all_stock_index_weekend;
            open cur_later_stock_index_weekend;
            loop
                fetch cur_all_stock_index_weekend
                    into all_stock_index_weekend;
                exit when cur_all_stock_index_weekend%notfound;

                fetch cur_later_stock_index_weekend
                    into later_stock_index_weekend;
                exit when cur_later_stock_index_weekend%notfound;

                -- 计算hei kin ashi平均K线开盘价，收盘价，最高价和最低价
                v_ha_index_weekend_open  := (all_stock_index_weekend.index_open +
                                            all_stock_index_weekend.index_close) / 2;
                v_ha_index_weekend_close := (later_stock_index_weekend.index_open +
                                            later_stock_index_weekend.index_close +
                                            later_stock_index_weekend.index_high +
                                            later_stock_index_weekend.index_low) / 4;
                v_ha_index_weekend_high  := fnc_ha_max_value(later_stock_index_weekend.index_high,
                                                             later_stock_index_weekend.index_open,
                                                             later_stock_index_weekend.index_close);
                v_ha_index_weekend_low   := fnc_ha_min_value(later_stock_index_weekend.index_low,
                                                             later_stock_index_weekend.index_open,
                                                             later_stock_index_weekend.index_close);
                -- 保存hei kin ashi平均K线开盘价，收盘价，最高价和最低价
                update stock_index_weekend t
                   set t.ha_index_open  = v_ha_index_weekend_open,
                       t.ha_index_close = v_ha_index_weekend_close,
                       t.ha_index_high  = v_ha_index_weekend_high,
                       t.ha_index_low   = v_ha_index_weekend_low
                 where t.index_code = later_stock_index_weekend.index_code
                   and t.index_begin_date =
                       later_stock_index_weekend.index_begin_date
                   and t.index_end_date =
                       later_stock_index_weekend.index_end_date;
                commit;
            end loop;
            close cur_all_stock_index_weekend;
            close cur_later_stock_index_weekend;

        end loop;
        close cur_all_index_code;
    end CAL_STK_IDX_WKD_HA_BD;

end PKG_INDEX_WEEKEND;